package org.unisinos.gc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.List;

import org.unisinos.gc.vo.Congresso;

public abstract class CongressoDAO {

	public static Congresso getCongresso() {
		return new Congresso();
	}
	
	public static boolean isCongressoAberto() {
		boolean congressoAberto = false;
		try {
			Statement stmt = ConnectionManager.getConnection().createStatement();
			// query no banco de exemplo
			congressoAberto = stmt.execute("select count(*) from congresso");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return congressoAberto;
	}
	
	public static Collection<Congresso> getCongressoList() throws SQLException {
		Collection<Congresso> congressos = new ArrayList<Congresso>();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = ConnectionManager.getConnection();
			stmt = con.createStatement();
			
			rs = stmt.executeQuery("select * from congresso");
			while(rs.next()) {

				Congresso c = new Congresso();
				c.setFimAvaliacao(rs.getString("fimavaliacao"));
				c.setFimCongresso(rs.getString("fimcongresso"));
				c.setFimInscricao(rs.getString("fiminscricao"));
				c.setFimSubmissao(rs.getString("fimsubmissao"));
				c.setInicioAvaliacao(rs.getString("inicioavaliacao"));
				c.setInicioCongresso(rs.getString("iniciocongresso"));
				c.setInicioInscricao(rs.getString("inicioinscricao"));
				c.setInicioSubmissao(rs.getString("iniciosubmissao"));
				c.setLocal(rs.getString("local"));
				c.setQtdeArtigosProrrogado(rs.getInt("qtdeartigosprorrogado"));
				c.setTitulo(rs.getString("titulo"));
				c.setId(rs.getInt("id"));
				congressos.add(c);
			}
		} finally {
			try {
				if (con!=null)con.close();
				if (stmt!=null)stmt.close();
				if (rs!=null)rs.close();
			}catch (Exception e) {}
		}
		return congressos;
	}
	
	public static Congresso getCongresso(int codigo) throws SQLException {
		Congresso c = null;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = ConnectionManager.getConnection();
			ps = con.prepareStatement("select * from congresso where id = ?");
			ps.setInt(1, codigo);
			rs = ps.executeQuery();
			while(rs.next()) {
				c = new Congresso();
				c.setFimAvaliacao(rs.getString("fimavaliacao"));
				c.setFimCongresso(rs.getString("fimcongresso"));
				c.setFimInscricao(rs.getString("fiminscricao"));
				c.setFimSubmissao(rs.getString("fimsubmissao"));
				c.setInicioAvaliacao(rs.getString("inicioavaliacao"));
				c.setInicioCongresso(rs.getString("iniciocongresso"));
				c.setInicioInscricao(rs.getString("inicioinscricao"));
				c.setInicioSubmissao(rs.getString("iniciosubmissao"));
				c.setLocal(rs.getString("local"));
				c.setQtdeArtigosProrrogado(rs.getInt("qtdeartigosprorrogado"));
				c.setTitulo(rs.getString("titulo"));
				c.setId(rs.getInt("id"));
				break;
			}
			
		} finally {
			try {
				if (con!=null)con.close();
				if (ps!=null)ps.close();
				if (rs!=null)rs.close();
			}catch (Exception e) {}
		}
		return c;
	}

	
	public static void addCongresso(Congresso congresso) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionManager.getConnection();
			String sql = "INSERT INTO congresso(" + 
			"fimavaliacao " +
			",fimcongresso " +
			",fiminscricao " +
			",fimsubmissao " +
			",inicioavaliacao " +
			",iniciocongresso " +
			",inicioinscricao " +
			",iniciosubmissao " +
			",local " +
			",qtdeartigosprorrogado " +
			",titulo " +
			",id) " +
			" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			ps = con.prepareStatement(sql);
			ps.setString(1, congresso.getFimAvaliacao());
			ps.setString(2, congresso.getFimCongresso());
			ps.setString(3, congresso.getFimInscricao());
			ps.setString(4, congresso.getFimSubmissao());
			ps.setString(5, congresso.getInicioAvaliacao());
			ps.setString(6, congresso.getInicioCongresso());
			ps.setString(7, congresso.getInicioInscricao());
			ps.setString(8, congresso.getInicioSubmissao());
			ps.setString(9, congresso.getLocal());
			ps.setInt(10, congresso.getQtdeArtigosProrrogado());
			ps.setString(11, congresso.getTitulo());
			ps.setInt(12, congresso.getId());
			ps.execute();
		} finally {
			try {
				if (con!=null)con.close();
				if (ps!=null)ps.close();
			}catch (Exception e) {}
		}
	}
	
	public static void deleteCongresso(int codigo) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionManager.getConnection();
			ps = con.prepareStatement("delete from congresso where id = ?");
			ps.setInt(1, codigo);
			ps.execute();
		} finally {
			try {
				if (con!=null)con.close();
				if (ps!=null)ps.close();
			}catch (Exception e) {}
		}
	}
	
	public static void updateCongresso(Congresso congresso) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionManager.getConnection();
			ps = con.prepareStatement("UPDATE congresso SET fimavaliacao = ? " +
					", fimcongresso = ? " +
					", fiminscricao = ? " +
					", fimsubmissao = ? " +
					", inicioavaliacao = ? " +
					", iniciocongresso = ? " +
					", inicioinscricao = ? " +
					", iniciosubmissao = ? " +
					", local = ? " +
					", titulo= ? " +
					", qtdeartigosprorrogado = ? " +
					"WHERE id=?");
			ps.setString(1, congresso.getFimAvaliacao());
			ps.setString(2, congresso.getFimCongresso());
			ps.setString(3, congresso.getFimInscricao());
			ps.setString(4, congresso.getFimSubmissao());
			ps.setString(5, congresso.getInicioAvaliacao());
			ps.setString(6, congresso.getInicioCongresso());
			ps.setString(7, congresso.getInicioInscricao());
			ps.setString(8, congresso.getInicioSubmissao());
			ps.setString(9, congresso.getLocal());
			ps.setString(10, congresso.getTitulo());
			ps.setInt(11, congresso.getQtdeArtigosProrrogado());
			ps.setInt(12, congresso.getId());
			ps.execute();
		} finally {
			try {
				if (con!=null)con.close();
				if (ps!=null)ps.close();
			}catch (Exception e) {}
		}
	}
	
	public static void deleteCongressoArea(int codigo) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionManager.getConnection();
			ps = con.prepareStatement("delete from congresso_area where codigo_congresso = ?");
			ps.setInt(1, codigo);
			ps.execute();
		} finally {
			try {
				if (con!=null)con.close();
				if (ps!=null)ps.close();
			}catch (Exception e) {}
		}
	}
	public static Integer[] loadCongressoArea(int codigo) throws SQLException {
		List<Integer> areas = new ArrayList<Integer>(); 
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = ConnectionManager.getConnection();
			ps = con.prepareStatement("select * from congresso_area where codigo_congresso = ?");
			ps.setInt(1, codigo);
			rs = ps.executeQuery();
			while(rs.next()) {
				areas.add(rs.getInt("codigo_area"));
			}
			Integer areasArray[] = new Integer[areas.size()];
			for(int i = 0; i<areas.size(); i++){
				areasArray[i] = areas.get(i);
			}
			return areasArray;
		} finally {
			try {
				if (con!=null)con.close();
				if (ps!=null)ps.close();
			}catch (Exception e) {}
		}
	}
	
	public static void insertCongressoArea(int codigoCongresso, int codigoArea) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionManager.getConnection();
			ps = con.prepareStatement(" insert into congresso_area (codigo_congresso, codigo_area) values (? , ?)");
			ps.setInt(1, codigoCongresso);
			ps.setInt(2, codigoArea);
			ps.execute();
		} finally {
			try {
				if (con!=null)con.close();
				if (ps!=null)ps.close();
			}catch (Exception e) {}
		}
	}
}
